package com.ruoyi.know.mapper;

import com.ruoyi.know.dto.PostTrendDTO;
import com.ruoyi.know.dto.SectionDistributionDTO;
import com.ruoyi.know.dto.UserPointsRankDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface StatisticsMapper {

    // 帖子数量趋势
    @Select("WITH RECURSIVE date_range AS (" +
            "    SELECT CURDATE() - INTERVAL 6 DAY AS date " +
            "    UNION ALL " +
            "    SELECT date + INTERVAL 1 DAY " +
            "    FROM date_range " +
            "    WHERE date < CURDATE() " +
            ") " +
            "SELECT dr.date, IFNULL(posts.count, 0) AS count " +
            "FROM date_range dr " +
            "LEFT JOIN (" +
            "    SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS date, COUNT(*) AS count " +
            "    FROM community_posts " +
            "    WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) " +
            "    GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d') " +
            ") posts ON dr.date = posts.date")
    List<PostTrendDTO> selectPostTrend();

    // 板块帖子分布
    @Select("SELECT s.section_name AS sectionName, COUNT(p.post_id) AS postCount " +
            "FROM community_sections s LEFT JOIN community_posts p ON s.section_id = p.section_id " +
            "GROUP BY s.section_id")
    List<SectionDistributionDTO> selectSectionDistribution();

    // 用户积分排名
    @Select("SELECT nick_name AS nickName, points FROM sys_user ORDER BY points DESC LIMIT 5")
    List<UserPointsRankDTO> selectUserPointsRank();
}
